﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using clEntidad;
using System.Collections;

namespace clBD
{
    public class BDRegistroLlave:BDBase 
    {
        public BDRegistroLlave(OleDbConnection cn):base(cn){ }
        public BDRegistroLlave(OleDbConnection cn, OleDbTransaction trx) : base(cn, trx) { }
        public int  grabar(RegistroLlave elemento, int idLogin)
        {
            elemento.id = getSeq("SEQ_REG_LLAVE");
            string insert = "insert into reg_llave " +
                " (id,num_doc,ape_pat,ape_mat,nombres,empresa,entrega,id_loc,id_login_entrega," +
                " llav_codigo,llav_ambiente,llav_cant1,llav_cant2,autoriza,motivo,observ,movil,llav_cant3,observaciones_dev ) " +
                " values(" + elemento.id + ",'" + elemento.persona.numDoc.Trim().ToUpper() + "','"
                + elemento.persona.apePat.Trim().ToUpper() + "','"
                + elemento.persona.apeMat.Trim().ToUpper() + "','"
                + elemento.persona.nombres.Trim().ToUpper() + "','"
                + elemento.persona.empresa.Trim().ToUpper() + "'"
                + ",now,"
                + elemento.idLocal.ToString() + ","
                + idLogin
                + ",'" + elemento.llave.codigo
                + "','" + elemento.llave.detalle 
                +"'," + elemento.llave.cant1.ToString()
                + ","  +elemento.llave.cant2.ToString()
                + ",'" + elemento.autoriza 
                + "','"+ elemento.motivo 
                +"','"+ elemento.observaciones
                +"','"+ elemento.persona.numMovil
                +"'," + elemento.llave.cant3 
                +",'" + elemento.observaciones_dev 
                +"')";

            execute(insert);
            return elemento.id;
        }
        public List<RegistroLlave> getByFec(DateTime fecha, int idLocal)
        {
            List<RegistroLlave> elems = new List<RegistroLlave>();
            DateTime fecFin = fecha.AddDays(1);
            String query = "SELECT id,entrega,num_doc, ape_pat,ape_mat,nombres,empresa" +
                ",autoriza,motivo,movil,llav_codigo,llav_ambiente,llav_cant1,llav_cant2,observ" +
                ",devolucion, llav_cant3, observaciones_dev FROM "
                + "reg_llave where entrega>=? and entrega<? and id_loc=? order by id";
            OleDbCommand comm = new OleDbCommand(query, this.cn);

            comm.Parameters.Add(new OleDbParameter("fecha", OleDbType.Date, 0, "fecha"));
            comm.Parameters["fecha"].Value = fecha;

            comm.Parameters.Add(new OleDbParameter("fecFin", OleDbType.Date, 0, "fecFin"));
            comm.Parameters["fecFin"].Value = fecFin;

            comm.Parameters.Add(new OleDbParameter("id_loc", OleDbType.Numeric, 0, "id_loc"));
            comm.Parameters["id_loc"].Value = idLocal;

            OleDbDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                RegistroLlave elem = new RegistroLlave();
                elem.id = dr.GetInt32(0);
                elem.entrada = dr.GetDateTime(1);

                elem.persona.numDoc = dr.GetString(2);
                elem.persona.apePat = dr.GetString(3);
                elem.persona.apeMat = dr.GetString(4);
                elem.persona.nombres = dr.GetString(5);

                elem.persona.empresa = dr.GetString(6);
                elem.autoriza = dr.GetString(7);
                elem.motivo = dr.GetString(8);
                elem.persona.numMovil = dr.GetString(9);
                elem.llave.codigo = dr.GetString(10);
                elem.llave.detalle = dr.GetString(11);
                elem.llave.cant1 = dr.GetInt16(12);
                elem.llave.cant2 = dr.GetInt16(13);
                elem.observaciones = dr.GetString(14);

                if (!dr.IsDBNull(15))
                    elem.salida = dr.GetDateTime(15);
                elem.llave.cant3 = dr.GetInt16(16);
                if (!dr.IsDBNull(17))
                    elem.observaciones_dev = dr.GetString(17);
                /*
                if (!dr.IsDBNull(8))
                {
                    //elem.salio = true;
                    elem.salida= dr.GetDateTime(8);
                }*/
                elems.Add(elem);
            }
            return elems;

        }

        public RegistroLlave get(int id)
        {
            RegistroLlave elem = new RegistroLlave();
            String query = "SELECT id,entrega,num_doc, ape_pat,ape_mat,nombres,empresa" +
                ",autoriza,motivo,movil,llav_codigo,llav_ambiente,llav_cant1,llav_cant2,observ" +
                ",devolucion,llav_cant3 FROM "
                + "reg_llave where id=?";
            OleDbCommand comm = new OleDbCommand(query, this.cn);

            comm.Parameters.Add(new OleDbParameter("id", OleDbType.Integer, 0, "id"));
            comm.Parameters["id"].Value = id;

            OleDbDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                elem.id = dr.GetInt32(0);
                elem.entrada = dr.GetDateTime(1);

                elem.persona.numDoc = dr.GetString(2);
                elem.persona.apePat = dr.GetString(3);
                elem.persona.apeMat = dr.GetString(4);
                elem.persona.nombres = dr.GetString(5);

                elem.persona.empresa = dr.GetString(6);
                elem.autoriza = dr.GetString(7);
                elem.motivo = dr.GetString(8);
                elem.persona.numMovil = dr.GetString(9);
                elem.llave.codigo = dr.GetString(10);
                elem.llave.detalle = dr.GetString(11);
                elem.llave.cant1 = dr.GetInt16(12);
                elem.llave.cant2 = dr.GetInt16(13);
                elem.observaciones = dr.GetString(14);
                if (!dr.IsDBNull(15))
                    elem.salida = dr.GetDateTime(15);
                elem.llave.cant3 = dr.GetInt16(16);
            }
            return elem;
        }
        public void devolucion(int id, int idLogin, String observacion)
        {
            String update = "update reg_llave set devolucion=now,id_login_devolucion=?,observaciones_dev=? where id=?";

            OleDbCommand comm = new OleDbCommand(update, this.cn);

            OleDbParameter parIdLogin = new OleDbParameter("@id_login_devolucion", OleDbType.BigInt);
            parIdLogin.Value = idLogin;
            comm.Parameters.Add(parIdLogin);
            OleDbParameter parObsDev = new OleDbParameter("@observaciones_dev", OleDbType.VarChar);
            parObsDev.Value = observacion;
            comm.Parameters.Add(parObsDev);

            OleDbParameter parId = new OleDbParameter("@id", OleDbType.BigInt);
            parId.Value = id;
            comm.Parameters.Add(parId);


            comm.ExecuteNonQuery();
        }
        public ArrayList reporte()
        {
            String query = "SELECT id as [Id],entrega as [Entrega]," +
                "num_doc as [N° Doc#], ape_pat as [Ape# Paterno],ape_mat  as [Ape# Materno]," +
                "nombres as [Nombres],empresa as [Empresa]," +
                "autoriza as [Cod# Autor],motivo as [Motivo]," + 
                "movil as [Num# Cel],llav_codigo as [Cod# Llave],llav_ambiente as [Local],"+
                "llav_cant1 as [Cantidad] ,llav_cant2 as [Cant# M],llav_cant3 as [Cant# E]," + 
                "observ as [Observaciones Entrega],devolucion as [Devolución], observaciones_dev as [Observaciones devolución] " + 
                "FROM reg_llave";
            try
            {
                return getArrayListWithHeader(query);
            }
            catch (OleDbException ex)
            {
                throw new SystemException("Error al obtener reporte de llaves", ex);
            }
        }

 
    }

}
